#!/usr/bin/env python 
# -*- coding: utf-8 -*- 
# @Time : 2018-06-11 10:03 
# @Author : Leo
# @File : ExportDB3toMysql.py 


import sqlite3
import pymysql

DB3_PATH = 'E:\\DB3样例数据\\Tandfbook\\tandfbook_20180514.db3'
MYSQL_CONFIG = {
    'host': '192.168.30.170',
    'port': 3306,
    'user': 'root',
    'passwd': 'vipdatacenter',
    'db': 'tandfbook'
}

"""
`lngid` TEXT, 
  `rawid` TEXT, 
  `identifier_doi` TEXT, 
  `title` TEXT, 
  `title_sub` TEXT, 
  `title_alternative` TEXT, 
  `title_edition` TEXT, 
  `title_series` TEXT, 
  `identifier_pisbn` TEXT, 
  `identifier_eisbn` TEXT, 
  `identifier_pissn` TEXT, 
  `identifier_eissn` TEXT, 
  `identifier_cnno` TEXT, 
  `identifier_standard` TEXT, 
  `creator` TEXT, 
  `creator_en` TEXT, 
  `creator_bio` TEXT, 
  `creator_institution` TEXT, 
  `creator_discipline` TEXT, 
  `creator_degree` TEXT, 
  `creator_drafting` TEXT, 
  `creator_release` TEXT, 
  `contributor` TEXT, 
  `creator_cluster` TEXT, 
  `agency` TEXT, 
  `gch` TEXT, 
  `source` TEXT, 
  `source_en` TEXT, 
  `source_institution` TEXT, 
  `publisher` TEXT, 
  `volume` TEXT, 
  `issue` TEXT, 
  `bookid` TEXT, 
  `description` TEXT, 
  `description_en` TEXT, 
  `description_unit` TEXT, 
  `description_fund` TEXT, 
  `description_core` TEXT, 
  `description_cycle` TEXT, 
  `description_type` TEXT, 
  `subject` TEXT, 
  `subject_en` TEXT, 
  `provider_subject` TEXT, 
  `page` TEXT, 
  `subject_clc` TEXT, 
  `subject_esc` TEXT, 
  `subject_dsa` TEXT, 
  `subject_csc` TEXT, 
  `subject_isc` TEXT, 
  `date` TEXT, 
  `date_impl` TEXT, 
  `date_created` TEXT, 
  `language` TEXT, 
  `country` TEXT, 
  `provider` TEXT, 
  `provider_url` TEXT, 
  `provider_id` TEXT, 
  `cover` TEXT, 
  `agents` TEXT, 
  `legal_status` TEXT, 
  `province_code` TEXT, 
  `folio_size` TEXT, 
  `price` TEXT, 
  `type` TEXT, 
  `medium` TEXT, 
  `batch` TEXT);
"""

# dealSqlQuote_func = lambda text: text.replace(r"'", r"''") if isinstance(text, str) else text

dealSqlQuote_func = lambda text: "'" + text.replace(r"'", r"''").replace('&', '/&').strip('\\') + "'" if text is not None else 'null'


def getItemsFromDB3(conn):
    cursor = conn.cursor()
    Sql = "SELECT * FROM modify_title_info_zt"
    cursor.execute(Sql)
    result = cursor.fetchall()
    for item in result:
        yield item


def Insert2Msyql(conn, items_list):
    cursor = conn.cursor()
    for item in items_list:
        # print(item[0])
        # Sql = "INSERT IGNORE INTO allfields VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
        # Sql = Sql % item
        Sql = "INSERT IGNORE INTO allfields VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        Sql = Sql % item
        print(Sql)
        cursor.execute(Sql)
    conn.commit()
    cursor.close()


def start():
    db3_conn = sqlite3.connect(DB3_PATH)
    pymysql_conn = pymysql.connect(host=MYSQL_CONFIG['host'], port=MYSQL_CONFIG['port'], user=MYSQL_CONFIG['user'], passwd=MYSQL_CONFIG['passwd'], db=MYSQL_CONFIG['db'],
                                   charset='utf8mb4')
    items_gen = getItemsFromDB3(db3_conn)
    items_temp = []
    print('START.')
    for each in items_gen:
        insert_data = [dealSqlQuote_func(field) for field in each]
        items_temp.append(tuple(insert_data))
        if len(items_temp) > 100:
            Insert2Msyql(pymysql_conn, items_temp)
            items_temp.clear()
    Insert2Msyql(pymysql_conn, items_temp)
    db3_conn.close()
    pymysql_conn.close()


if __name__ == '__main__':
    start()
